const database = require("../databaseConfig/index");
const bcrypt = require("bcryptjs");
const jx = require("../jwt");
const dayjs = require("dayjs");
const { v4: uuidv4 } = require("uuid");

// 登录
exports.login = async (req) => {
  const { username, password } = req;

  if (!username || !password) {
    const error = new Error("用户名和密码不能为空");
    error.status = 400;
    throw error;
  }

  try {
    const sql = `SELECT * FROM admin_users WHERE username = ?`;
    const results = await new Promise((resolve, reject) => {
      database.query(sql, [username], (err, results) => {
        if (err) reject(err);
        else resolve(results);
      });
    });
    if (results.length !== 1) {
      const error = new Error("用户名或密码错误");
      error.status = 401;
      throw error;
    }

    const compareResult = bcrypt.compareSync(password, results[0].password);
    if (!compareResult) {
      const error = new Error("用户名或密码错误");
      error.status = 401;
      throw error;
    }

    const { id, username: name, permissions } = results[0];
    const user = { id, username: name, permissions };
    const tokenStr = await jx.signJWT(user);

    return {
      id,
      username: name,
      permissions:permissions,
      access_token: tokenStr,
    };
  } catch (err) {
    const error = new Error(err.msg || "登录失败");
    error.status = err.status || 500;
    throw error;
  }
};

// 获取客户列表
exports.getCustomerList = async (req) => {
  const {
    pageNum = 1,
    pageSize = 10,
    enterpriseName,
    address,
    mainBusiness,
    applicationScenario,
    status,
    createTimeStart,
    createTimeEnd,
  } = req;

  try {
    let sql = `SELECT * FROM customers WHERE 1=1`;
    let countSql = `SELECT COUNT(*) as total FROM customers WHERE 1=1`;
    const params = [];
    const countParams = [];

    if (enterpriseName) {
      sql += ` AND enterpriseName LIKE ?`;
      countSql += ` AND enterpriseName LIKE ?`;
      params.push(`%${enterpriseName}%`);
      countParams.push(`%${enterpriseName}%`);
    }

    if (address) {
      sql += ` AND address LIKE ?`;
      countSql += ` AND address LIKE ?`;
      params.push(`%${address}%`);
      countParams.push(`%${address}%`);
    }

    if (mainBusiness) {
      sql += ` AND mainBusiness LIKE ?`;
      countSql += ` AND mainBusiness LIKE ?`;
      params.push(`%${mainBusiness}%`);
      countParams.push(`%${mainBusiness}%`);
    }

    if (applicationScenario) {
      sql += ` AND applicationScenario = ?`;
      countSql += ` AND applicationScenario = ?`;
      params.push(applicationScenario);
      countParams.push(applicationScenario);
    }

    if (status !== undefined) {
      sql += ` AND status = ?`;
      countSql += ` AND status = ?`;
      params.push(status);
      countParams.push(status);
    }

    if (createTimeStart && createTimeEnd) {
      sql += ` AND create_time BETWEEN ? AND ?`;
      countSql += ` AND create_time BETWEEN ? AND ?`;
      params.push(createTimeStart, createTimeEnd);
      countParams.push(createTimeStart, createTimeEnd);
    }

    sql += ` LIMIT ? OFFSET ?`;
    params.push(parseInt(pageSize), (pageNum - 1) * pageSize);

    const [list, totalResult] = await Promise.all([
      new Promise((resolve, reject) => {
        database.query(sql, params, (err, results) => {
          if (err) reject(err);
          else resolve(results);
        });
      }),
      new Promise((resolve, reject) => {
        database.query(countSql, countParams, (err, results) => {
          if (err) reject(err);
          else resolve(results[0].total);
        });
      }),
    ]);

    // 获取每个客户的联系人
    for (const customer of list) {
      const contacts = await new Promise((resolve, reject) => {
        database.query(
          `SELECT * FROM customerContacts WHERE customerId = ?`,
          [customer.id],
          (err, results) => {
            if (err) reject(err);
            else resolve(results);
          }
        );
      });
      contacts.forEach(e => {
        if(e.isPrimary==1){
          e.isPrimary = true
        }else{
          e.isPrimary = false
        }
      });

      customer.contacts = contacts;
    }
    for (const customer of list) {
      // 获取上游供应商
      const upstreamSuppliers = await new Promise((resolve, reject) => {
        database.query(
          `SELECT name FROM customerSupplyChain 
           WHERE customerId = ? AND type = 0`,
          [customer.id],
          (err, results) => {
            if (err) reject(err);
            else resolve(results.map((item) => item.name));
          }
        );
      });

      // 获取下游客户
      const downstreamCustomers = await new Promise((resolve, reject) => {
        database.query(
          `SELECT name FROM customerSupplyChain 
           WHERE customerId = ? AND type = 1`,
          [customer.id],
          (err, results) => {
            if (err) reject(err);
            else resolve(results.map((item) => item.name));
          }
        );
      });

      customer.upstreamSuppliers = upstreamSuppliers;
      customer.downstreamCustomers = downstreamCustomers;
    }

    return {
      list,
      pageNum: parseInt(pageNum),
      pageSize: parseInt(pageSize),
      total: totalResult,
    };
  } catch (err) {
    const error = new Error(err.message || "获取客户列表失败");
    error.status = err.status || 500;
    throw error;
  }
};

exports.addCustomer = async (req) => {
  const {
    enterpriseName,
    contacts,
    logo,
    upstreamSuppliers,
    downstreamCustomers,

    ...restData
  } = req;

  // 验证逻辑保持不变...
  if (!enterpriseName) {
    const error = new Error("企业名称不能为空");
    error.status = 400;
    throw error;
  }

  try {
    const customerId = `cust_${uuidv4().replace(/-/g, "").substr(0, 10)}`;
    const now = dayjs().format("YYYY-MM-DD HH:mm:ss");

    const customerData = {
      id: customerId,
      enterpriseName: enterpriseName,
      address: restData.address || null,
      logo: logo || null,
      mainBusiness: restData.mainBusiness || null,
      applicationScenario: restData.applicationScenario || null,
      annualOutputValue: restData.annualOutputValue || null,
      employeeCount: restData.employeeCount || null,
      trainingExperience: restData.trainingExperience || null,
      otherInfo: restData.otherInfo || null,
      resourceInfo: restData.resourceInfo || null,
      confusedQuestions: restData.confusedQuestions || null,
      status: restData.status || 0,
      createTime: now,
      updateTime: now,
    };

    // 3. 使用参数化查询插入客户数据
    const customerFields = Object.keys(customerData);
    const customerValues = customerFields.map((field) => customerData[field]);

    await new Promise((resolve, reject) => {
      database.query(
        `INSERT INTO customers (${customerFields.join(", ")}) 
         VALUES (${customerFields.map(() => "?").join(", ")})`,
        customerValues,
        (err, results) => {
          if (err) {
            console.error("客户插入错误:", err.sqlMessage);
            reject(new Error(`数据库错误: ${err.sqlMessage}`));
          } else {
            resolve(results);
          }
        }
      );
    });

    // 4. 处理联系人逻辑保持不变...
    for (const contact of contacts) {
      const contactId = `cont_${uuidv4().replace(/-/g, "").substr(0, 10)}`;
      const contactData = {
        id: contactId,
        customerId: customerId,
        name: contact.name,
        position: contact.position || null,
        phone: contact.phone || null,
        trainingExperience: contact.trainingExperience || null,
        remarks: contact.remarks || null,
        isPrimary: contact.isPrimary ? 1 : 0,
        createTime: now,
        updateTime: now,
      };

      const contactFields = Object.keys(contactData);
      const contactValues = contactFields.map((field) => contactData[field]);

      await new Promise((resolve, reject) => {
        database.query(
          `INSERT INTO customerContacts (${contactFields.join(", ")}) 
           VALUES (${contactFields.map(() => "?").join(", ")})`,
          contactValues,
          (err, results) => {
            if (err) reject(err);
            else resolve(results);
          }
        );
      });
    }
    for (const supplier of upstreamSuppliers) {
      await new Promise((resolve, reject) => {
        database.query(
          `INSERT INTO customerSupplyChain SET ?`,
          {
            id: `sc_${uuidv4().replace(/-/g, "")}`,
            customerId,
            type: 0, // 0表示上游供应商
            name: supplier,
            createTime: now,
          },
          (err, results) => {
            if (err) reject(err);
            else resolve(results);
          }
        );
      });
    }

    // 添加下游客户
    for (const customer of downstreamCustomers) {
      await new Promise((resolve, reject) => {
        database.query(
          `INSERT INTO customerSupplyChain SET ?`,
          {
            id: `sc_${uuidv4().replace(/-/g, "")}`,
            customerId,
            type: 1, // 1表示下游客户
            name: customer,
            createTime: now,
          },
          (err, results) => {
            if (err) reject(err);
            else resolve(results);
          }
        );
      });
    }

    return customerId;
  } catch (err) {
    console.error("完整错误:", err.message);
    throw err; // 保持原始错误堆栈
  }
};
exports.updateCustomer = async (req) => {
  const { id, upstreamSuppliers = [], downstreamCustomers = [], logo, contacts = [], ...updateData } = req;
  
  if (!id) {
    const error = new Error("客户ID不能为空");
    error.status = 400;
    throw error;
  }

  try {
    // 1. 检查客户是否存在
    const [customer] = await new Promise((resolve, reject) => {
      database.query(
        `SELECT id FROM customers WHERE id = ? LIMIT 1`,
        [id],
        (err, results) => {
          if (err) return reject(err);
          resolve(results);
        }
      );
    });

    if (!customer) {
      const error = new Error("客户不存在");
      error.status = 404;
      throw error;
    }

    // 2. 初始化事务
    const connection = await new Promise((resolve, reject) => {
      database.getConnection((err, conn) => {
        if (err) return reject(err);
        resolve(conn);
      });
    });

    try {
      await new Promise((resolve, reject) => {
        connection.beginTransaction(err => {
          if (err) return reject(err);
          resolve();
        });
      });

      const now = dayjs().format("YYYY-MM-DD HH:mm:ss");

      // 3. 更新客户基本信息
      if (Object.keys(updateData).length > 0) {
        const updateFields = [];
        const params = [];
        
        for (const [key, value] of Object.entries(updateData)) {
          if (key === 'id' || key === 'create_time') continue;
          
          if (value === undefined || value === null) {
            updateFields.push(`${key} = NULL`);
          } else {
            updateFields.push(`${key} = ?`);
            params.push(value);
          }
        }

        if (updateFields.length > 0) {
          updateFields.push("updateTime = ?");
          params.push(now, id); // 最后添加WHERE条件

          await new Promise((resolve, reject) => {
            connection.query(
              `UPDATE customers SET ${updateFields.join(", ")} WHERE id = ?`,
              params,
              (err) => {
                if (err) reject(err);
                else resolve();
              }
            );
          });
        }
      }

      // 4. 更新logo（如果有）
      if (logo !== undefined) {
        await new Promise((resolve, reject) => {
          connection.query(
            `UPDATE customers SET logo = ?, updateTime = ? WHERE id = ?`,
            [logo, now, id],
            (err) => {
              if (err) reject(err);
              else resolve();
            }
          );
        });
      }

      // 5. 更新联系人信息
      await updateContactsTransaction(connection, id, contacts, now);

      // 6. 更新供应链信息
      await updateSupplyChainTransaction(
        connection, 
        id, 
        upstreamSuppliers, 
        downstreamCustomers, 
        now
      );

      // 提交事务
      await new Promise((resolve, reject) => {
        connection.commit(err => {
          if (err) return reject(err);
          resolve();
        });
      });

      return { success: true, message: "客户信息更新成功" };
    } catch (err) {
      // 回滚事务
      await new Promise((resolve) => {
        connection.rollback(() => resolve());
      });
      throw err;
    } finally {
      connection.release();
    }
  } catch (err) {
    console.error("更新客户失败:", err);
    const error = new Error(err.message || "更新客户失败");
    error.status = err.status || 500;
    throw error;
  }
};

// 事务中更新联系人
async function updateContactsTransaction(connection, customerId, contacts, now) {
  // 1. 获取现有联系人ID
  const existingContacts = await new Promise((resolve, reject) => {
    connection.query(
      `SELECT id FROM customerContacts WHERE customerId = ?`,
      [customerId],
      (err, results) => {
        if (err) return reject(err);
        resolve(results.map(c => c.id));
      }
    );
  });

  // 2. 分离新旧联系人
  const contactsToUpdate = contacts.filter(c => c.id && existingContacts.includes(c.id));
  const contactsToAdd = contacts.filter(c => !c.id || !existingContacts.includes(c.id));
  const contactsToDelete = existingContacts.filter(id => 
    !contacts.some(c => c.id === id)
  );

  // 3. 删除不再需要的联系人
  if (contactsToDelete.length > 0) {
    await new Promise((resolve, reject) => {
      connection.query(
        `DELETE FROM customerContacts WHERE id IN (?)`,
        [contactsToDelete],
        (err) => {
          if (err) return reject(err);
          resolve();
        }
      );
    });
  }

  // 4. 更新现有联系人
  for (const contact of contactsToUpdate) {
    await new Promise((resolve, reject) => {
      connection.query(
        `UPDATE customerContacts SET 
          name = ?, 
          position = ?, 
          phone = ?, 
          trainingExperience = ?, 
          remarks = ?, 
          isPrimary = ?, 
          updateTime = ? 
         WHERE id = ?`,
        [
          contact.name,
          contact.position || null,
          contact.phone || null,
          contact.trainingExperience || null,
          contact.remarks || null,
          contact.isPrimary ? 1 : 0,
          now,
          contact.id
        ],
        (err) => {
          if (err) return reject(err);
          resolve();
        }
      );
    });
  }

  // 5. 添加新联系人
  for (const contact of contactsToAdd) {
    const contactId = `cont_${uuidv4().replace(/-/g, "").substring(0, 10)}`;
    await new Promise((resolve, reject) => {
      connection.query(
        `INSERT INTO customerContacts SET ?`,
        {
          id: contactId,
          customerId,
          name: contact.name,
          position: contact.position || null,
          phone: contact.phone || null,
          trainingExperience: contact.trainingExperience || null,
          remarks: contact.remarks || null,
          isPrimary: contact.isPrimary ? 1 : 0,
          createTime: now,
          updateTime: now
        },
        (err) => {
          if (err) return reject(err);
          resolve();
        }
      );
    });
  }
}

// 事务中更新供应链
async function updateSupplyChainTransaction(
  connection, 
  customerId, 
  upstreamSuppliers, 
  downstreamCustomers, 
  now
) {
  // 1. 删除旧的供应链数据
  await new Promise((resolve, reject) => {
    connection.query(
      `DELETE FROM customerSupplyChain WHERE customerId = ?`,
      [customerId],
      (err) => {
        if (err) return reject(err);
        resolve();
      }
    );
  });

  // 2. 添加上游供应商
  for (const supplier of upstreamSuppliers) {
    if (!supplier) continue;
    await new Promise((resolve, reject) => {
      connection.query(
        `INSERT INTO customerSupplyChain SET ?`,
        {
          userId: `sc_${uuidv4().replace(/-/g, "")}`,
          customerId,
          type: 0,
          name: supplier,
          createTime: now
        },
        (err) => {
          if (err) return reject(err);
          resolve();
        }
      );
    });
  }

  // 3. 添加下游客户
  for (const customer of downstreamCustomers) {
    if (!customer) continue;
    await new Promise((resolve, reject) => {
      connection.query(
        `INSERT INTO customerSupplyChain SET ?`,
        {
          id: `sc_${uuidv4().replace(/-/g, "")}`,
          customerId,
          type: 1,
          name: customer,
          createTime: now
        },
        (err) => {
          if (err) return reject(err);
          resolve();
        }
      );
    });
  }
}


// 删除客户
exports.deleteCustomer = async (req) => {
  const { id } = req;

  if (!id || (Array.isArray(id) && id.length === 0)) {
    const error = new Error("客户ID不能为空");
    error.status = 400;
    throw error;
  }

  try {
    const ids = Array.isArray(id) ? id : [id];

    // 使用事务确保数据一致性
    await new Promise((resolve, reject) => {
      database.getConnection((err, connection) => {
        if (err) return reject(err);

        connection.beginTransaction(async (err) => {
          if (err) {
            connection.release();
            return reject(err);
          }

          try {
            // 1. 删除联系人
            await new Promise((resolve, reject) => {
              connection.query(
                `DELETE FROM customerContacts WHERE customerId IN (?)`,
                [ids],
                (err, results) => {
                  if (err) reject(err);
                  else resolve(results);
                }
              );
            });

            // 2. 删除上下游关系
            await new Promise((resolve, reject) => {
              connection.query(
                `DELETE FROM customerSupplyChain WHERE customerId IN (?)`,
                [ids],
                (err, results) => {
                  if (err) reject(err);
                  else resolve(results);
                }
              );
            });

            // 3. 删除客户
            await new Promise((resolve, reject) => {
              connection.query(
                `DELETE FROM customers WHERE id IN (?)`,
                [ids],
                (err, results) => {
                  if (err) reject(err);
                  else resolve(results);
                }
              );
            });

            connection.commit((err) => {
              if (err) {
                return connection.rollback(() => {
                  connection.release();
                  reject(err);
                });
              }
              connection.release();
              resolve();
            });
          } catch (err) {
            connection.rollback(() => {
              connection.release();
              reject(err);
            });
          }
        });
      });
    });

    return null;
  } catch (err) {
    const error = new Error(err.msg || "删除客户失败");
    error.status = err.status || 500;
    throw error;
  }
};

// 修改客户状态
exports.changeCustomerStatus = async (req) => {
  const { id, status } = req;

  if (status !== 0 && status !== 1) {
    const error = new Error("状态值不合法 (0-正常, 1-禁用)");
    error.status = 400;
    throw error;
  }

  try {
    await new Promise((resolve, reject) => {
      database.query(
        `UPDATE customers SET status = ?, updateTime = ? WHERE id = ?`,
        [status, dayjs().format("YYYY-MM-DD HH:mm:ss"), id],
        (err, results) => {
          if (err) reject(err);
          else resolve(results);
        }
      );
    });

    return null;
  } catch (err) {
    const error = new Error(err.msg || "修改状态失败");
    error.status = err.status || 500;
    throw error;
  }
};

